---
title: "cp_external_client_lookup stored procedure documentation"
summary: "Retrieved SQL Server procedure that returns the latest matching external-client registrant row."
status: draft
owner: "SAIF Claims BOF team"
last_reviewed: "2026-08-25"
source_schema: "dbo"
source_name: "cp_external_client_lookup"
source_type: "PROCEDURE"
retrieval_status: "found"
retrieval_command: "legends sqlserver query --name cp_external_client_lookup"
retrieval_date: "2026-08-25"
---
# cp_external_client_lookup

## Overview

| Field | Value |
| --- | --- |
| **Schema** | `dbo` |
| **Type** | Standalone procedure |
| **Database** | SQL Server (`eisD0P11`) |
| **Calling libraries** | `SAIF.Business.View.Policy` |
| **Calling classes** | `clsTeamAssignment` |

## Purpose

`cp_external_client_lookup` looks up an external-client registrant row from `EIS_PROD.dbo.Net_Registrant`. The retrieved body filters on trimmed ID and last-name equality, excludes inactive and test users, and returns the most recently updated match.

## Procedures / Methods

### cp_external_client_lookup

| Field | Value |
| --- | --- |
| **Returns** | Result set containing the matching `Net_Registrant` row |
| **Called by** | `clsTeamAssignment.InitializeEmailAddress` |
| **Description** | Returns the latest active non-test registrant row that matches the supplied client ID and last name. |

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `@idNum` | IN | `char(100)` | — | Client or user ID number to match. |
| `@clientLastName` | IN | `char(255)` | — | Client last name to match. |

## Business Rules

The retrieved database source embeds the following server-side rules.

| Rule ID | Summary |
| --- | --- |
| [BR-VP-041](#br-vp-041-external-client-lookup-returns-the-latest-active-non-test-registrant-that-matches-the-supplied-id-and-last-name) | The procedure returns `TOP 1` from `Net_Registrant` after trimmed ID and last-name matching, restricted to active non-test rows and ordered by latest update date. |

### BR-VP-041 External-client lookup returns the latest active non-test registrant that matches the supplied ID and last name

| Field | Value |
| --- | --- |
| **Statement** | The external-client lookup returns only rows whose trimmed `ID_NUM` and `USER_LAST_NAM` match the input values, whose `ACTIVE_FL = 'Y'`, whose `TEST_USER_FL = 'N'`, and whose `UPDATE_DATE` is greatest. |
| **Trigger** | `cp_external_client_lookup` executes. |
| **Enforcement** | The query filters on trimmed ID and last-name equality, active and non-test flags, orders by `reg.UPDATE_DATE DESC`, and selects `TOP 1 *`. |
| **Outcome** | Callers receive the most recently updated active production registrant row for the supplied identity values. |
| **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_external_client_lookup.json` |

## Operations

The retrieved database source implements the following internal operations.

| Operation | Description | Trigger |
| --- | --- | --- |
| [Return the latest matching external-client registrant row](#return-the-latest-matching-external-client-registrant-row) | Select the most recently updated active non-test `Net_Registrant` row that matches the supplied ID and last name. | Caller invokes `cp_external_client_lookup`. |

### Return the latest matching external-client registrant row

| Field | Value |
| --- | --- |
| **Description** | Select the most recently updated active non-test `Net_Registrant` row that matches the supplied ID and last name. |
| **Trigger** | Caller invokes `cp_external_client_lookup`. |
| **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_external_client_lookup.json` |

## Calling code references

| Class | Method | Source File |
| --- | --- | --- |
| `clsTeamAssignment` | `InitializeEmailAddress` | `SAIF.Business.View.Policy\clsTeamAssignment.vb` |

## Source Code

<details>
<summary>SQL Server procedure source</summary>

```sql

Create Procedure dbo.cp_external_client_lookup 
--	LIST PARAMETERS
	@idNum char(100),
	@clientLastName char(255)
AS

/*--------------------------------------------------------------------------------
Procedure Name:	cp_external_client_lookup
Date Written:	01/08/2007
Author:		Jerry Todd (JERTOD)
Installation:	SAIF Corporation
Source File: 	cp_external_client_lookup.sql 
Description:	This procedure looks up an external client's mailid based on the client's
		user number

		PARMS: client ID number and client Last Name
Modifications:
--------------------------------------------------------------------------------*/
set nocount on
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;


select top 1 * 
from EIS_PROD.dbo.Net_Registrant reg
where 
rtrim(reg.ID_NUM)=rtrim(@idNum)
and rtrim(reg.USER_LAST_NAM)=rtrim(@clientLastName)
and reg.ACTIVE_FL='Y'
AND reg.TEST_USER_FL='N'

order by reg.UPDATE_DATE desc


/* END THE PROCEDURE */

Return
```

</details>

## Related documentation
- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)

## Sources
- SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_external_client_lookup.json`
- Calling code: `SAIF.Business.View.Policy\clsTeamAssignment.vb`